package cn.openea.perpartner.rule.util;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.toolkit.SqlHelper;
import com.baomidou.mybatisplus.extension.toolkit.SqlRunner;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.StringJoiner;

/**
 * SQL执行器，实际是使用SqlRunner的方式执行原生SQL，不会污染Mapper
 * @author Weird077
 */
@Component
public class Rdb {
    /**
     * 执行一条 INSERT 语句，可包占位符。
     *
     * @param sql  insert("insert into sys_user(userid,username) values({0},{1})","10010","联通客服")
     * @param args sql参数值
     * @return true or false
     */
    public static boolean insert(String sql, Object... args) {
        return SqlRunner.db().insert(sql, args);
    }

    /**
     * 执行一条 DELETE 语句，可包占位符。
     * @param sql  delete("delete from sys_user where userid = {0}","10010")
     * @param args sql参数值
     * @return true or false
     */
    public static boolean delete(String sql, Object... args) {
        return SqlRunner.db().delete(sql, args);
    }

    /**
     * 根据主键删除数据库表记录
     * @param tableName 数据库表名
     * @param id 数据库表主键的值
     * @return true or false
     */
    public static boolean deleteById(String tableName, String id) {
        String sql = "delete from "+tableName+" where "+getKey(tableName)+"={0}";
        return delete(sql, id);
    }

    /**
     * 执行一条 UPDATE 语句，可包占位符。
     *
     * @param sql  update("update set username = {0} from sys_user where userid={1}","电信客服","10000");
     * @param args sql参数值
     * @return true or false
     */
    public static boolean update(String sql, Object... args) {
        return SqlRunner.db().update(sql, args);
    }

    /**
     * 以params参数里，数据库表主键字段为查询条件，更新记录
     * @param tableName 数据库表名
     * @param params    更新的参数
     * @return true or false
     */
    public static boolean updateById(String tableName, Map<String,Object> params) {
        StringBuilder columns = new StringBuilder();
        params.forEach((k,v)->columns.append(k).append("=").append("'").append(v).append("'").append(","));
        Object id = getKey(tableName);
        String sql = "update " + tableName + " set " +columns.deleteCharAt(columns.length() - 1)+ "" + "  where "+id+"= {0} )";
        return update(sql, id);
    }

    /**
     * 执行 SELECT 语句，可包含占位符，返回多条记录
     *
     * @param sql  selectList("select * from sys_user where userid={0}", "10010")
     * @param args 参数值
     * @return Map结果集
     */
    public static List<Map<String, Object>> selectList(String sql, Object... args) {
        return SqlRunner.db().selectList(sql, args);
    }

    /**
     * 执行 SELECT 语句，可包含占位符，返回多条记录
     *
     * @param sql  selectObjs("select * from sys_user where userid={0}", "10010")
     * @param args 参数值
     * @return list集合
     */
    public static List<Object> selectObjs(String sql, Object... args) {
        return SqlRunner.db().selectObjs(sql, args);
    }

    /**
     * 执行 SELECT 语句，可包含占位符，返回一个Object。若查询行数不为1时，则会抛 SQLException 异常。
     *
     * @param sql  selectObj("select * from sys_user where userid={0}", "10010")
     * @param args 参数值
     * @return object
     */
    public static Object selectObj(String sql, Object... args) {
        return SqlRunner.db().selectObj(sql, args);
    }

    /**
     * @param sql  selectCount("select count(1) from sys_user where userid>{0}", "10010")
     * @param args String格式
     * @return
     */
    public static long selectCount(String sql, Object... args) {
        return SqlRunner.db().selectCount(sql, args);
    }
    /**
     * 执行 SELECT 语句，可包含占位符，返回一个Map<String, Object>。若查询行数不为1时，则会抛 SQLException 异常。
     *
     * @param sql  selectOne("select * from sys_user where userid={0}", "10010")
     * @param args 参数值
     * @return Map<String, Object>
     */
    public static Map<String, Object> selectOne(String sql, Object... args) {
        return SqlRunner.db().selectOne(sql, args);
    }
    public static Map<String, Object> selectById(String tableName, String id) {
        String sql = "select * from "+tableName+" where "+getKey(tableName)+" = {0}";
        return selectOne(sql, id);
    }

    /**
     * 执行 SELECT 语句，可包含占位符，返回多条记录
     *
     * @param page 分页对象 new Page<>(1,10)或new PageDTO<>(1,10)
     * @param sql  selectPage(new Page<>(1,10),"select * from sys_user")
     * @param args 参数值
     * @return list
     */
    public static List selectPage(Page page, String sql, Object... args) {
        return SqlRunner.db().selectPage(page, sql, args).getRecords();
    }

    /**
     * 保存：根据tableName和params拼装insert语句，最终执行insert操作;
     * 更新：（1）若columns不为空，则根据tableName和params、以及columns拼装update语句,最终执行update操作;
     *      （2）若tableName的主键字段在params里不为null,则updateById；
     * 更新时，columns主要传入主键字段即可，且必须包含在params里面
     * @param tableName 数据库表名称，不允许为空
     * @param params   Map 参数
     * @param columns  where条件字段，update操作时，需要在params里存在
     * @return true or false
     */
    public static boolean saveOrUpdate(String tableName,Map<String, Object> params,Object... columns) {
        if (columns.length>0){
            StringBuilder updateColumns = new StringBuilder();
            params.forEach((k,v)->updateColumns.append(k).append("=").append("'").append(v).append("'").append(","));
            StringBuilder whereSql = new StringBuilder();
            for (Object column : columns) {
                whereSql.append(" and ").append(column).append("=").append("'").append(params.get(column)).append("'");
            }
            String sql = "update " + tableName + " set " +updateColumns.deleteCharAt(updateColumns.length() - 1)+ "" + " where 1=1 " + whereSql +")";
            return update(sql);
        }
        Object id = getKey(tableName);
        if(params.containsKey(id) && !Objects.isNull(params.get(id))){
            StringBuilder builder = new StringBuilder();
            params.forEach((k,v)->builder.append(k).append("=").append("'").append(v).append("'").append(","));
            String sql = "update " + tableName + " set " +builder.deleteCharAt(builder.length() - 1)+ "" + "  where "+id+"= {0} )";
            return update(sql, id);
        }
        StringJoiner keys = new StringJoiner(",");
        StringBuilder values = new StringBuilder();
        params.forEach((k, v) -> {
            keys.add(k);
            values.append("'").append(v).append("'").append(",");
        });
        String sql = "insert into " + tableName + "(" + keys + ")" + " values" + "(" + values + ")";
        return insert(sql);
    }

    /**
     * 获取数据库表的主键
     * @param tableName 数据表名
     * @return 数据表主键
     */
    private static Object getKey(String tableName){
        StringBuilder sql = new StringBuilder();
        try {
            String dbName = SqlSessionUtils.getSqlSession(SqlHelper.FACTORY).getConnection().getCatalog();
            sql.append("select col.column_name from ");
            sql.append("information_schema.table_constraints tab,");
            sql.append("information_schema.key_column_usage col ");
            sql.append(" where col.constraint_name = tab.constraint_name ");
            sql.append(" and col.table_name = tab.table_name ");
            sql.append(" and col.TABLE_SCHEMA = '").append(dbName).append("'");
            sql.append(" and tab.TABLE_SCHEMA = '").append(dbName).append("'");
            sql.append(" and tab.constraint_type = 'primary key' ");
            sql.append(" and col.table_name = '").append(tableName).append("'");
        } catch (SQLException e) {
           e.printStackTrace();
        }
       return selectObj(sql.toString());
    }
}
